package hyung.jin.seo;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

public class ConnectionManager {
	public static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
	public static final String CONNECTION_URL = "jdbc:derby:lotto;create=true";
//	public static final String CONNECTION_URL = "jdbc:derby://localhost:1527/lotto;create=true";
	public static final String TABLE = "stat";

	private Connection con;

	public ConnectionManager() {
		super();
		try {
			Class.forName(DRIVER).newInstance();
			System.out.println("Loaded the appropriate driver ");
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void connect() throws SQLException {
		con = DriverManager.getConnection(CONNECTION_URL);
		System.out.println("Connection is successful");
	}

	public void disconnect() throws SQLException {
		con.close();
		System.out.println("Connection is disconnected");
	}
	
	public int getRecentDraw() throws SQLException
	{
		int draw = 0;
		String query = "SELECT MAX(DRAW) FROM stat";
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery(query);
		while (rs.next()) {
			draw = rs.getInt(1);
		}
		rs.close();
		st.close();
		return draw;
	}

	public int[] getResult(int draw) throws SQLException {
		int[] result = new int[7];
		String query = "SELECT NUM1, NUM2, NUM3, NUM4, NUM5, NUM6, SUPPLEMENT FROM stat WHERE DRAW = ?";
		PreparedStatement pstmt = con.prepareStatement(query);
		pstmt.setInt(1, draw);
		ResultSet rs = pstmt.executeQuery();
		int cnt = 0;
		while (rs.next()) {
			result[cnt++] = rs.getInt(1);
			result[cnt++] = rs.getInt(2);
			result[cnt++] = rs.getInt(3);
			result[cnt++] = rs.getInt(4);
			result[cnt++] = rs.getInt(5);
			result[cnt++] = rs.getInt(6);
			result[cnt++] = rs.getInt(7);
		}
		rs.close();
		pstmt.close();
		return result;
	}

	public int[] getResult() throws SQLException {
		return getResult(getRecentDraw());
	}
	
	public int[] getPopularity() throws SQLException
	{
		int[] popular = new int[7];
		int[] nums = getMostPopularNums();
		int supplement = getMostPopularSupplement();
		for(int i=0; i<nums.length; i++)
		{
			popular[i] = nums[i];
		}
		popular[popular.length-1] = supplement;
		return popular;
	}

	private int getMostPopularNums(int num) throws SQLException {
		int cnt = 0;
		String query = "SELECT COUNT(*) FROM stat WHERE NUM1 = " + num
				+ " OR NUM2 = " + num + " OR NUM3 = " + num + " OR NUM4 = "
				+ num + " OR NUM5 = " + num + " OR NUM6 = " + num;
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery(query);
		while (rs.next()) {
			cnt = rs.getInt(1);
			// System.out.println(query + " : Total number is " + cnt);
		}
		rs.close();
		st.close();
		return cnt;
	}

	public int[] getMostPopularNums() throws SQLException {
		int[] result = new int[6];
		int[] nums = new int[45];
		int[] temp = new int[45];
		for (int i = 0; i < nums.length; i++) 
		{
			nums[i] = getMostPopularNums(i+1);
			temp[i] = nums[i];
		}
		Arrays.sort(temp);
		
		int[] popular = new int[6];
		int cnt = 1;
		for(int j=0; j<popular.length; j++)
		{
			popular[j] = temp[temp.length-cnt++];
		}
		
		// look for the index
		result = getIndexPopularNum(popular, nums);
		for(int k=0; k < result.length; k++)
		{
			result[k] = result[k]+1;
		}
		return result;
	}
	
	private int[] getIndexPopularNum(int[] value, int[] nums)
	{
		int[] index = new int[value.length];
		first : for(int i=0; i < value.length; i++)
		{
			second : for(int j=0; j < nums.length; j++)
			{
				if((nums[j]==value[i])&&(!isContained(index, j)))
				{
					index[i] = j;
					break second;
				}
			}
		}
		return index;
	}
	
	private boolean isContained(int[] index, int value)
	{
		boolean flag = false;
		for(int i = 0; i<index.length; i++)
		{
			if(value==index[i])
			{
				flag = true;
				break;
			}
		}
		return flag;
	}

	private int getMostPopularSupplement(int num) throws SQLException {
		int cnt = 0;
		String query = "SELECT COUNT(*) FROM stat WHERE SUPPLEMENT = " + num;
		// String query = "SELECT COUNT(*) FROM stat WHERE NUM6 = " + num;
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery(query);
		while (rs.next()) {
			cnt = rs.getInt(1);
			System.out.println(query + " : Supplement number is " + cnt);
		}
		rs.close();
		st.close();
		return cnt;
	}

	private int getMostPopularSupplement() throws SQLException {
		int popular = 0;
		int value = 0;
		for (int i = 1; i < 46; i++) {
			int cnt = getMostPopularSupplement(i);
			if (cnt > value) {
				value = cnt;
				popular = i;
			}
		}
		return popular;
	}

	public static void main(String[] args) {
		ConnectionManager cm = new ConnectionManager();
		try {
			cm.connect();
			cm.display(cm.getPopularity());
			cm.disconnect();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	private void display(int[] input) {
		System.out.println();
		for (int i = 0; i < input.length; i++) {
			System.out.print(input[i] + "\t");
		}
		System.out.println();
	}

}
